export const postgres10CreateScript = `
  SELECT
  'CREATE TABLE ' || quote_ident(tabdef.schema_name) || '.' || quote_ident(tabdef.table_name) || E' (\n' ||
  array_to_string(
    array_agg(
      '  ' || quote_ident(tabdef.column_name) || ' ' ||
      case when tabdef.def_val like 'nextval(%_seq%' then
        case when tabdef.type = 'integer' then 'serial'
            when tabdef.type = 'smallint' then 'smallserial'
            when tabdef.type = 'bigint' then 'bigserial'
            else tabdef.type end
      else
        tabdef.type
      end || ' ' ||
      tabdef.not_null ||
      CASE WHEN tabdef.def_val IS NOT NULL
                AND NOT (tabdef.def_val like 'nextval(%_seq%'
                        AND (tabdef.type = 'integer' OR tabdef.type = 'smallint' OR tabdef.type = 'bigint'))
          THEN ' DEFAULT ' || tabdef.def_val
      ELSE '' END ||
      CASE WHEN tabdef.identity IS NOT NULL THEN ' ' || tabdef.identity ELSE '' END
      ORDER BY tabdef.column_idx ASC
    )
    , E',\n'
  ) || E'\n);\n' ||
  CASE WHEN tc.constraint_name IS NULL THEN ''
      ELSE E'\nALTER TABLE ' || quote_ident(tabdef.schema_name) || '.' || quote_ident(tabdef.table_name) ||
      ' ADD CONSTRAINT ' || quote_ident(tc.constraint_name)  ||
      ' PRIMARY KEY ' || '(' || substring(constr.column_name from 0 for char_length(constr.column_name)-1) || ')'
  END AS createtable
  FROM
  ( SELECT
    c.relname AS table_name,
    a.attname AS column_name,
    a.attnum AS column_idx,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
    CASE
      WHEN a.attnotnull OR a.attidentity != '' THEN 'NOT NULL'
    ELSE 'NULL'
    END AS not_null,
    CASE WHEN a.atthasdef THEN pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS def_val,
    CASE WHEN a.attidentity = 'a' THEN 'GENERATED ALWAYS AS IDENTITY' when a.attidentity = 'd' THEN 'GENERATED BY DEFAULT AS IDENTITY' ELSE null END AS identity,
    n.nspname as schema_name
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_attribute a ON (a.attnum > 0 AND a.attrelid = c.oid)
  JOIN pg_type t ON (a.atttypid = t.oid)
  LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
  WHERE c.relname = $1
  AND n.nspname = $2
  ORDER BY a.attnum DESC
  ) AS tabdef
  LEFT JOIN information_schema.table_constraints tc
  ON  tc.table_name       = tabdef.table_name
  AND tc.table_schema     = tabdef.schema_name
  AND tc.constraint_Type  = 'PRIMARY KEY'
  LEFT JOIN LATERAL (
  SELECT column_name || ', ' AS column_name
  FROM   information_schema.key_column_usage kcu
  WHERE  kcu.constraint_name = tc.constraint_name
  AND kcu.table_name = tabdef.table_name
  AND kcu.table_schema = tabdef.schema_name
  ORDER BY ordinal_position
  ) AS constr ON true
  GROUP BY tabdef.schema_name, tabdef.table_name, tc.constraint_name, constr.column_name;

`;

export const defaultCreateScript = `
  SELECT
  'CREATE TABLE ' || quote_ident(tabdef.schema_name) || '.' || quote_ident(tabdef.table_name) || E' (\n' ||
  array_to_string(
    array_agg(
      '  ' || quote_ident(tabdef.column_name) || ' ' ||
      case when tabdef.def_val like 'nextval(%_seq%' then
        case when tabdef.type = 'integer' then 'serial'
            when tabdef.type = 'smallint' then 'smallserial'
            when tabdef.type = 'bigint' then 'bigserial'
            else tabdef.type end
      else
        tabdef.type
      end || ' ' ||
      tabdef.not_null ||
      CASE WHEN tabdef.def_val IS NOT NULL
                AND NOT (tabdef.def_val like 'nextval(%_seq%'
                        AND (tabdef.type = 'integer' OR tabdef.type = 'smallint' OR tabdef.type = 'bigint'))
          THEN ' DEFAULT ' || tabdef.def_val
      ELSE '' END ||
      CASE WHEN tabdef.identity IS NOT NULL THEN ' ' || tabdef.identity ELSE '' END
      ORDER BY tabdef.column_idx ASC
    )
    , E',\n'
  ) || E'\n);\n' ||
  CASE WHEN tc.constraint_name IS NULL THEN ''
      ELSE E'\nALTER TABLE ' || quote_ident(tabdef.schema_name) || '.' || quote_ident(tabdef.table_name) ||
      ' ADD CONSTRAINT ' || quote_ident(tc.constraint_name)  ||
      ' PRIMARY KEY ' || '(' || substring(constr.column_name from 0 for char_length(constr.column_name)-1) || ')'
  END AS createtable
  FROM
  ( SELECT
    c.relname AS table_name,
    a.attname AS column_name,
    a.attnum AS column_idx,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS type,
    CASE
      WHEN a.attnotnull THEN 'NOT NULL'
    ELSE 'NULL'
    END AS not_null,
    CASE WHEN a.atthasdef THEN pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) ELSE null END AS def_val,
    null::text as identity,
    n.nspname as schema_name
  FROM pg_class c
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  JOIN pg_attribute a ON (a.attnum > 0 AND a.attrelid = c.oid)
  JOIN pg_type t ON (a.atttypid = t.oid)
  LEFT JOIN pg_attrdef ad ON (a.attrelid = ad.adrelid AND a.attnum = ad.adnum)
  WHERE c.relname = $1
  AND n.nspname = $2
  ORDER BY a.attnum DESC
  ) AS tabdef
  LEFT JOIN information_schema.table_constraints tc
  ON  tc.table_name       = tabdef.table_name
  AND tc.table_schema     = tabdef.schema_name
  AND tc.constraint_Type  = 'PRIMARY KEY'
  LEFT JOIN LATERAL (
  SELECT column_name || ', ' AS column_name
  FROM   information_schema.key_column_usage kcu
  WHERE  kcu.constraint_name = tc.constraint_name
  AND kcu.table_name = tabdef.table_name
  AND kcu.table_schema = tabdef.schema_name
  ORDER BY ordinal_position
  ) AS constr ON true
  GROUP BY tabdef.schema_name, tabdef.table_name, tc.constraint_name, constr.column_name;

`;
